package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.model.X_MM_Waybill;
import org.compiere.model.X_MM_WaybillLine;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_457 extends SvrProcess {
	//Form_457_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_MM_Driver_ID; 
	
	Timestamp p_MM_DateOf_IssueBegin; 
	Timestamp p_MM_DateOf_IssueEnd; 

	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("MM_Driver_ID")) 
				p_MM_Driver_ID  = para[i].getParameterAsInt();
			else if (name.equals("MM_DateOf_IssueBegin"))
				p_MM_DateOf_IssueBegin = (Timestamp) para[i].getParameter();
			else if (name.equals("MM_DateOf_IssueEnd"))
				p_MM_DateOf_IssueEnd = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		
	}


	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_457_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		// Cell Styling
		CellStyle ds = wb.createCellStyle();
		ds.setBorderBottom((short)1);
		ds.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		ds.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		ds.setAlignment(CellStyle.ALIGN_CENTER);
		
		CellStyle fs = wb.createCellStyle();
		fs.setBorderBottom((short)1);
		fs.setBorderTop((short)1);
		fs.setBorderLeft((short)1);
		fs.setBorderRight((short)1);
//		fs.setDataFormat(wb.createDataFo);
		
		CellStyle dateStyle = wb.createCellStyle();
		dateStyle.setBorderBottom((short)1);
		dateStyle.setBorderTop((short)1);
		dateStyle.setBorderLeft((short)1);
		dateStyle.setBorderRight((short)1);
//		DataFormat dataFormat = wb.createDataFormat();
//		dateStyle.setDataFormat(dataFormat.getFormat("hh:mm"));
		
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom((short)1);
		mergeStyle.setBorderTop((short)1);
		mergeStyle.setBorderLeft((short)1);
		mergeStyle.setBorderRight((short)1);
		mergeStyle.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		mergeStyle.setWrapText(true);
		mergeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		//Header
		
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		
		Calendar cal = Calendar.getInstance();
		Date d = new Date(cal.getTimeInMillis());
		
		sheet.getRow(14).getCell(51).setCellValue(cal.get(Calendar.DATE));
		sheet.getRow(14).getCell(54).setCellValue((new SimpleDateFormat("MMMM").format(d)));
		sheet.getRow(14).getCell(60).setCellValue(cal.get(Calendar.YEAR));
		
		sheet.getRow(18).getCell(28).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueBegin)));
		sheet.getRow(18).getCell(34).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueEnd)));
		sheet.getRow(19).getCell(0).setCellValue((new MBPartner(m_ctx, p_MM_Driver_ID, get_TrxName()).getName()));
		
		//Positions
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT MM_Waybill_ID FROM adempiere.MM_Waybill " + 
					"WHERE MM_Driver_ID = " + p_MM_Driver_ID + 
					" AND MM_DateOf_Issue >= '" + new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueBegin) + "'" + 
					" AND MM_DateOf_Issue <= '" + new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueEnd) + "'";
		
		try {
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 25;
		
			while (rs.next()) {
				X_MM_Waybill waybill = new X_MM_Waybill(m_ctx, rs.getInt(1), get_TrxName());
				
				//Shifting and Merging
				sheet.shiftRows(row, row + 14, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 4, 8));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 9, 12));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 13, 17));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 25));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 26, 29));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 32));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 33, 36));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 43));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 44, 46));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 47, 50));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 53));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 54, 57));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 58, 60));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 61, 64));
				
				R = sheet.getRow(row);
				for(int i=0; i<=64;i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				PreparedStatement pstmtcost = null;
				ResultSet rscost = null;
				String sqlcost = "SELECT CurrentCostPrice FROM adempiere.M_Cost " + 
								"WHERE M_Costelement_ID = 1000000 AND M_Product_ID = " + waybill.getMM_FuelMark_ID();
				double dcost = 0;
				
				try {
					pstmtcost = DB.prepareStatement(sqlcost.toString(), get_TrxName());
					rscost = pstmtcost.executeQuery();
					
					if (rscost.next()){
						dcost = (double) rscost.getInt(1);
					}
				}
				catch (Exception e) {
					log.log(Level.SEVERE, sqlcost.toString(), get_TrxName());
				}
				finally {
					DB.close(rscost, pstmtcost);
					rscost = null;
					pstmtcost = null;
				}
				
				sheet.getRow(row).getCell(0).setCellValue(waybill.getDocumentNo());
				sheet.getRow(row).getCell(4).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(waybill.getMM_DateOf_Issue())));
				sheet.getRow(row).getCell(9).setCellValue((new MProduct(m_ctx, waybill.getMM_FuelMark_ID(), get_TrxName()).getName()));
				sheet.getRow(row).getCell(13).setCellValue(waybill.getMM_Vehicle_RegNo());
				sheet.getRow(row).getCell(18).setCellValue(waybill.getMM_ConsumpRate_Vehicle100km());
				sheet.getRow(row).getCell(30).setCellValue(waybill.getMM_FuelRemainder_Checkout());
				sheet.getRow(row).getCell(33).setCellValue(waybill.getMM_FuelRemainder_Checkout() * dcost);
				sheet.getRow(row).getCell(37).setCellValue(waybill.getMM_Fuel_Issued());
				sheet.getRow(row).getCell(40).setCellValue(waybill.getMM_Fuel_Issued() * dcost);
				int Consumption = (waybill.getMM_FuelRemainder_Checkout() + waybill.getMM_Fuel_Issued()) - waybill.getMM_FuelRemainder_Return();
				sheet.getRow(row).getCell(44).setCellValue(Consumption);
				sheet.getRow(row).getCell(47).setCellValue(Consumption * dcost);
				sheet.getRow(row).getCell(58).setCellValue(waybill.getMM_FuelRemainder_Return());
				sheet.getRow(row).getCell(61).setCellValue(waybill.getMM_FuelRemainder_Return() * dcost);
				
				PreparedStatement pstmtwbl = null;
				ResultSet rswbl = null;
				String sqlwbl = "SELECT MM_Waybillline_ID FROM MM_Waybillline " + 
				"WHERE MM_Waybill_ID = " + rs.getInt(1);
				
				try {
					pstmtwbl = DB.prepareStatement(sqlwbl.toString(), get_TrxName());
					rswbl = pstmtwbl.executeQuery();
					
					while (rswbl.next()) {
						X_MM_WaybillLine waybillline = new X_MM_WaybillLine(m_ctx, rswbl.getInt(1), get_TrxName());

						float cr = ((float) waybillline.getMM_Result_Mileage_Total() / 100) * waybill.getMM_ConsumpRate_Vehicle100km();
						sheet.getRow(row).getCell(51).setCellValue(cr);
						sheet.getRow(row).getCell(54).setCellValue(cr * dcost);
						sheet.getRow(row).getCell(26).setCellValue(waybillline.getMM_Result_Mileage_Total());
					}
				}
				catch (Exception e) {
					log.log(Level.SEVERE, sqlwbl.toString(), get_TrxName());
				}
				finally {
					DB.close(rswbl, pstmtwbl);
					rswbl = null;
					pstmtwbl = null;
				}
				row++;
			}
			
			//Totals
			sheet.getRow(row).getCell(26).setCellFormula("SUM(AA26:AA" + row + ")");
			sheet.getRow(row).getCell(30).setCellFormula("SUM(AE26:AE" + row + ")");
			sheet.getRow(row).getCell(33).setCellFormula("SUM(AH26:AH" + row + ")");
			sheet.getRow(row).getCell(37).setCellFormula("SUM(AL26:AL" + row + ")");
			sheet.getRow(row).getCell(40).setCellFormula("SUM(AO26:AO" + row + ")");
			sheet.getRow(row).getCell(44).setCellFormula("SUM(AS26:AS" + row + ")");
			sheet.getRow(row).getCell(47).setCellFormula("SUM(AV26:AV" + row + ")");
			sheet.getRow(row).getCell(51).setCellFormula("SUM(AZ26:AZ" + row + ")");
			sheet.getRow(row).getCell(54).setCellFormula("SUM(BC26:BC" + row + ")");
			sheet.getRow(row).getCell(58).setCellFormula("SUM(BG26:BG" + row + ")");
			sheet.getRow(row).getCell(61).setCellFormula("SUM(BJ26:BJ" + row + ")");
			
//			sheet.getRow(row + 4).getCell(17).setCellValue((new MBPartner(m_ctx, p_M_Driver_ID, get_TrxName()).getName()));
//			sheet.getRow(row + 6).getCell(17).setCellValue((new MBPartner(m_ctx, p_M_Driver_ID, get_TrxName()).getName()));
/**
			PreparedStatement pstmtjob = null;
			ResultSet rsjob = null;
			String sqljob = "SELECT Name FROM adempiere.HR_Job " + 
						"WHERE HR_Job_ID = ( SELECT HR_Job_ID FROM adempiere.HR_Employee " + 
						"WHERE C_BPartner_ID = " + p_M_Prepared_ID;
			try {
				pstmtjob = DB.prepareStatement(sqljob.toString(), get_TrxName());
				rsjob = pstmtjob.executeQuery();
				
				if (rsjob.next()) 
					sheet.getRow(row + 4).getCell(5).setCellValue(rsjob.getString(1));
			}
			catch (Exception e) {
				log.log(Level.SEVERE, sqljob.toString(), e);
			}
			finally {
				DB.close(rsjob, pstmtjob);
				rsjob = null;
				pstmtjob = null;
			}
			
			PreparedStatement pstmtjob = null;
			ResultSet rsjob = null;
			String sqljob = "SELECT Name FROM adempiere.HR_Job " + 
						"WHERE HR_Job_ID = ( SELECT HR_Job_ID FROM adempiere.HR_Employee " + 
						"WHERE C_BPartner_ID = " + p_M_Checked_ID;
			try {
				pstmtjob = DB.prepareStatement(sqljob.toString(), get_TrxName());
				rsjob = pstmtjob.executeQuery();
				
				if (rsjob.next()) 
					sheet.getRow(row + 6).getCell(5).setCellValue(rsjob.getString(1));
			}
			catch (Exception e) {
				log.log(Level.SEVERE, sqljob.toString(), e);
			}
			finally {
				DB.close(rsjob, pstmtjob);
				rsjob = null;
				pstmtjob = null;
			}
*/
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
	
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();

		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
